from application.config.mysql import db_config
import pymysql


# 执行原生 SQL
def native_sql(sql):
    connection = pymysql.connect(**db_config)  # 数据库连接【一般为公共，但本方法使用少】
    cursor = connection.cursor()  # 创建游标对象
    try:
        res = cursor.execute(sql)  # 执行语句
        connection.commit()  # 提交事务
        return res
    except Exception as e:
        print("Error:", e)
        connection.rollback()  # 回滚事务
    finally:
        cursor.close()  # 关闭游标
        connection.close()  # 关闭连接


# 执行原生 SQL,返回游标结果
def native_sql_cursor(sql):
    connection = pymysql.connect(**db_config)  # 数据库连接【一般为公共，但本方法使用少】
    cursor = connection.cursor()  # 创建游标对象
    try:
        cursor.execute(sql)  # 执行语句
        res2 = cursor.fetchone()[0]
        connection.commit()  # 提交事务
        return res2
    except Exception as e:
        print("Error:", e)
        connection.rollback()  # 回滚事务
    finally:
        cursor.close()  # 关闭游标
        connection.close()  # 关闭连接


# 复制表结构
def copy_structure(new_table, old_table):
    sql = f"CREATE TABLE {new_table} LIKE {old_table}"
    print('复制表结构', sql)
    res = native_sql(sql)  # 0执行成功
    return res


# 复制数据
def copy_data(new_table, old_table):
    sql = f"INSERT INTO {new_table} SELECT * FROM {old_table}"
    print('复制数据', sql)
    res = native_sql(sql)  # 大于0执行成功，数值代表复制条数
    return res


# 删除表
def del_table(table):
    sql = f"DROP TABLE {table}"
    res = native_sql(sql)  # 0执行成功
    return res


# 表大小
def count_table(table):
    sql = f"SELECT COUNT(*) FROM {table}"
    print('表大小', sql)
    count = native_sql_cursor(sql)
    return count


if __name__ == '__main__':
    # copy_data('Template_prize_result4', 'Template_prize_result')
    # count_table('activityapp_activity')
    # del_table('Template_prize_result4')
    sql = "INSERT INTO Template_prize_result (openid, phone, model, is_prize, prize_name, shopping_code, prize_type, prize_rand, create_time, activity_id) VALUES ('ok9HS5SXICv_GmPG1q0yGVLIjqL8', '15210722329', '3022', 0, '未中奖', 'None', NULL, NULL, '2023-06-16 10:02:13', 4)"
    a = native_sql(sql)
    print(a)
